﻿using SuporeSliplampApp.Base;
using SuporeSliplampApp.Models;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Controls;
using System.Windows.Media.Imaging;
using System.Data.Sql;
using System.Data.SqlClient;
using static System.Windows.Forms.VisualStyles.VisualStyleElement.StartPanel;
using System.Windows;
//using System.Data.Sql;

namespace SuporeSliplampApp.DataAccess
{
    public class SqlHelper
    {
        public SqlConnection Conn { get; set; }

        public SqlCommand Comm { get; set; }
        public SqlDataAdapter adapter { get; set; }

        private void Dispose()
        {
            if (adapter != null)
            {
                adapter.Dispose();
                adapter = null;
            }
            if (Comm != null)
            {
                Comm.Dispose();
                Comm = null;
            }
            if (Conn != null)
            {
                Conn.Close();
                Conn.Dispose();
                Conn = null;
            }
        }

        private bool DBConnection()
        {
            string connStr = ConfigurationManager.ConnectionStrings["slip"].ConnectionString;
            if (Conn == null) Conn = new SqlConnection(connStr);

            try
            {
                Conn.Open();
                return true;
            }
            catch
            {

                return false;
            }
        }

        public bool CheckUserInfo(string userID,string pwd)
        {
            try
            {
                if (DBConnection())
                {
                    string userSql = "select * from user_tb where user_ID = @user_ID and pwd =@pwd";
                    adapter = new SqlDataAdapter(userSql, Conn);
                    adapter.SelectCommand.Parameters.Add(new SqlParameter(@"user_ID", SqlDbType.VarChar) { Value = userID});
                    adapter.SelectCommand.Parameters.Add(new SqlParameter(@"pwd", SqlDbType.VarChar) { Value=Md5Provider.GetMD5String(pwd+"@"+userID.ToLower())});
                    DataTable dt = new DataTable();
                    int count=adapter.Fill(dt);

                    if (count <= 0)
                    {
                        // throw new Exception("用户名或密码不正确");
                        return false;
                    }
                    //DataRow row = dt.Rows[0];
                    //if(row.Field<Int32>)
                    return true;
                }
            }
            catch (Exception ex)
            {

                //throw new Exception("用户名或密码不正确");
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool SavePic(string id,string name, string str)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"insert into pic_tb values ('{id}'),('{name}'),('{str}'),sysdate)";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if(res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
                //throw;
                
            }
            finally 
            {
                this.Dispose();
            }
            return false;
        }
        public bool SavePatient(PatientModel patient)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"insert into patient_tb(p_ID,p_name,sex,birth,adress,telphone,email,creator,insertDate,updateDate) values ('{patient.P_ID}','{patient.Name}','{patient.SEX}','{patient.Birth}','{patient.Adreess}'," +
                        $"'{patient.PhoneNum}','{patient.Email}','{patient.Creator}',GetDate(),GetDate())";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
                //throw;

            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        //public bool addPic(string path, string id)
        //{
        //    try
        //    {
        //        if (DBConnection())
        //        {
        //            string sql = $"insert into pic_tb (path,pic_ID) valus ";

        //            SqlCommand cmd = new SqlCommand(sql, Conn);
        //            int res = cmd.ExecuteNonQuery();
        //            if (res > 0)
        //                return true;
        //        }
        //    }
        //    catch (Exception)
        //    {
        //        return false;
        //        //throw;

        //    }
        //    finally
        //    {
        //        this.Dispose();
        //    }
        //    return false;
        //}

        public bool deletePic(string path,string id)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"delete from pic_tb where path ='{path}' and pic_ID ='{id}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
                //throw;

            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public List<string> getPicList(string id)
        {
            List<string> list = new List<string>();
            try
            {
                if (DBConnection())
                {
                    string sql = $" select path from pic_tb  where pdate =(select MAX(pdate)from pic_tb where pic_ID ='{id}')";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read()) 
                    {
                        string data = reader.GetString(0);
                        list.Add(data);
                    }
                    reader.Close();

                }
            }
            catch (Exception)
            {

                //throw;

            }
            finally
            {
                this.Dispose();
            }
            return list;
        }

        public bool SaveResults(ResultModel result)
        {
            bool res = false;
            int maxValue = 0;
            SqlTransaction sqlTransaction;
            if (DBConnection())
            {
                
                sqlTransaction = Conn.BeginTransaction();
                try
                {
                    string sql = $"select MAX(pic_Num) as no from pic_tb ";
                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    cmd.Transaction = sqlTransaction;
                    SqlDataReader reader = cmd.ExecuteReader();
                    if (reader.Read())
                    {
                        if ((!reader.IsDBNull(0)))
                        {
                            string s = reader.GetString(0);
                            //maxValue = reader.GetInt32(0);
                            if (string.IsNullOrEmpty(s))
                            {
                                maxValue ++;
                            }
                            else
                            {
                                maxValue = Convert.ToInt32(s);
                                maxValue++;
                            }
                        }
                    }
                    if (reader != null)
                    {
                        reader.Close();
                    }

                    string querySql = $"select count(*) from record_tb where rec_ID ='{result.Id}'";
                    SqlCommand Temcmd = new SqlCommand(querySql, Conn);
                    Temcmd.Transaction = sqlTransaction;

                    int count = 0;
                    try
                    {
                        count = Convert.ToInt32(Temcmd.ExecuteScalar());
                    }
                    catch(Exception ex)
                    { 
                        count = 0;
                    }
                    
                    if (reader != null)
                    {
                        reader.Close();
                    }
                    string pic_Num =maxValue.ToString();

                    if (count<=0)
                    {
                        string sql1 = $"insert into record_tb(rec_ID,results,cdate) values ('{result.Id}','{result.Description}','{result.Date}')";

                        SqlCommand cmd1 = new SqlCommand(sql1, Conn);
                        cmd1.Transaction = sqlTransaction;
                        cmd1.ExecuteNonQuery();
                    }
                   

                    string sql2 = $"insert into pic_tb(pic_ID,pic_Num,name,path,pdate) values ('{result.Id}',{pic_Num},'','{result.Path}','{result.Date}')";
                    SqlCommand cmd2 = new SqlCommand(sql2, Conn);
                    cmd2.Transaction = sqlTransaction;
                    cmd2.ExecuteNonQuery();

                    sqlTransaction.Commit();
                    res = true;
                }
                catch (Exception ex)
                {

                    sqlTransaction.Rollback();
                    res = false;
                }
                finally
                {
                   
                    this.Dispose();
                    
                }
                
            }
            return res;
            //try
            //{
            //    if (DBConnection())
            //    {

            //        string sql1 = $"insert into record_tb(rec_ID,results,cdate) values ('{result.Id}','{result.Description}','{result.Date}'";

            //        SqlCommand cmd1 = new SqlCommand(sql1, Conn);
            //        cmd1.ExecuteNonQuery();

            //        string sql2 = $"insert into pic_tb(pic_ID,pic_Num,name,path,pdate) values ('{result.Id}','','','{result.Path}','{result.Date}'";
            //        SqlCommand cmd2 = new SqlCommand(sql2, Conn);
            //        cmd2.ExecuteNonQuery();

            //        sqlTransaction.Commit();
            //        return true;

            //    }
            //}
            //catch (Exception)
            //{
            //    sqlTransaction.Rollback();
            //    return false;
            //    //throw;

            //}
            //finally
            //{
            //    this.Dispose();
            //}
            //return false;
        }

        public bool SaveUser(UserModel user)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"insert into user_tb(user_ID,user_name,pwd,is_admin) values ('{user.UserName}','{user.UserName}','{ Md5Provider.GetMD5String(user.Password + "@" + user.UserName.ToLower())}',{user.IsAdmin})";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
                //throw;

            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public bool EditPatientInfo(PatientModel patient,string currentid)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"update patient_tb set p_ID ='{patient.P_ID}',p_name ='{patient.Name}',sex ='{patient.SEX}',birth='{patient.Birth}',adress='{patient.Adreess}',telphone='{patient.PhoneNum}',email='{patient.Email}',updateDate = GETDATE()"+
                    $"where p_ID ='{currentid}'";
                    //$"'{patient.PhoneNum}','{patient.Email}','{patient.Creator}',GetDate(),GetDate())";
                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally 
            {
                this.Dispose();
            }
            return false;
        }

        public bool DeletePatient(PatientModel patient)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"delete from  patient_tb where p_ID ='{patient.P_ID}'";
                    string sql2 = $"delete from  pic_tb where pic_ID ='{patient.P_ID}'";
                    //$"'{patient.PhoneNum}','{patient.Email}','{patient.Creator}',GetDate(),GetDate())";
                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    SqlCommand cmd2 = new SqlCommand(sql2, Conn);
                    int res = cmd.ExecuteNonQuery();
                    int res2 = cmd2.ExecuteNonQuery();
                    if (res > 0)
                    {
                         return true;
                    }

                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }

        public List<PatientModel> GetPatients() 
        {
            try
            {
                if (DBConnection())
                {
                    string sql = "select * from patient_tb";
                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    SqlDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    List<PatientModel> patientList = new List<PatientModel>();
                    while (reader.Read()) 
                    {
                        patientList.Add(new PatientModel()
                        {
                            Name = reader["p_name"].ToString(),
                            P_ID = reader["p_ID"].ToString(),
                            SEX = reader["sex"].ToString(),
                            Birth = Convert.ToDateTime(reader["birth"]),
                            Adreess = reader["adress"].ToString(),
                            PhoneNum = reader["telphone"].ToString(),
                            Email = reader["email"].ToString(),
                            Creator = reader["creator"].ToString(),
                            InsertDate = Convert.ToDateTime(reader["insertDate"])
                        }); 
                    }
                    reader.Close();
                    return patientList;
                }
                return null;
            }
            catch (Exception)
            {
                throw;
            }
            finally 
            {
                this.Dispose(); 
            }
           
            
        }
        public string GetPic(string sdate)
        {
            var str = "";
            try
            {
                if (DBConnection())
                {
                    string sql = $"select * from  pic_tb where CONVERT(varchar(100),p_date,20) ={sdate} ";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    DataTable dt = new DataTable();
                    adapter.Fill(dt);
                    if (dt.Rows.Count > 0)
                    {
                        str = dt.Rows[0][0].ToString();
                        
                        /*if (!string.IsNullOrEmpty(str))
                        {
                            //返Base64码转成数组
                            byte[] imageBytes = Convert.FromBase64String(str);
                            //读入MemoryStream对象
                            MemoryStream memoryStream = new MemoryStream(imageBytes,0,imageBytes.Length);
                            memoryStream.Write(imageBytes,0, imageBytes.Length  );
                            //转成图片
                           BitmapImage image = new BitmapImage();
                            image.BeginInit();
                            image.StreamSource = memoryStream;
                            image.EndInit();
                        }*/
                    }
                    

                }
                
            }
            catch (Exception)
            {
                str = "";
                //return str;//
                //throw;

            }
            finally
            {
                this.Dispose();
            }

            return str;
        }

        public bool PatidExist( string patid)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"select count(*) from patient_tb where p_ID ='{patid}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);

                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0 )
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally 
            { 
                this.Dispose(); 
            }
            
            return false;
        }


        public bool PicExist(string id)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"select count(*) from pic_tb where pic_ID ='{id}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);

                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }

            return false;
        }


        public bool UserExist(string username)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"select count(*) from user_tb where user_ID ='{username}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);

                    int count = (int)cmd.ExecuteScalar();

                    if (count > 0)
                    {
                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }

            return false;
        }



        public bool UpdateUserPassword(string userName, string newPassword, int isAdmin)
        {
            try
            {
                if (DBConnection())
                {
                    string encryptedPassword = Md5Provider.GetMD5String(newPassword + "@" + userName.ToLower());
                    string sql = $"UPDATE user_tb SET pwd = '{encryptedPassword}', is_admin = {isAdmin} WHERE user_name = '{userName}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();

                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }

            return false;
        }

        public bool DeleteUser(UserModel user)
        {
            try
            {
                if (DBConnection())
                {
                    string sql = $"delete from  user_tb where user_name ='{user.UserName}'";

                    SqlCommand cmd = new SqlCommand(sql, Conn);
                    int res = cmd.ExecuteNonQuery();
                    if (res > 0)
                        return true;
                }
            }
            catch (Exception)
            {
                return false;
            }
            finally
            {
                this.Dispose();
            }
            return false;
        }


        public List<UserModel> GetAllUsers()
        {
            List<UserModel> users = new List<UserModel>();
            try
            {
                if (DBConnection())
                {
                    string sql = "select user_name,is_admin from user_tb";
                    SqlCommand cmd = new SqlCommand (sql, Conn);
                    SqlDataReader reader = cmd.ExecuteReader();
                    while (reader.Read())
                    {
                        UserModel user = new UserModel()
                        {
                            UserName = reader["user_name"].ToString(),
                            IsAdmin = Convert.ToInt32(reader["is_admin"])
                        };
                        users.Add(user);
                    }
                }
            }
            catch(Exception ex)
            {
                MessageBox.Show($"获取用户列表失败: {ex.Message}");
            }
            finally
            {
                this.Dispose();
            }
            return users;
        }



    }
}
